# $Id: Filter.pm,v 1.4 2004/06/20 08:22:24 ibo Exp $

package CGI::FOGE2::Filter;
use strict;
use CGI::FOGE2::Form;
use CGI::FOGE2::Tools;

our @ISA = ();

sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my %args = @_;

    my $self = {
	name => 'filter',
        form => $args{form},
	temp_table => undef,
    };

    bless $self, $class;
    return $self;
}

sub setform {
    my $self = shift;
    my ($form) = @_;
    
    print STDERR 'FILTER  setform: ', $form->{title}, "\n";
    
    $self->{form} = $form;
}


sub serve {
    my $self = shift;
    my $form = $self->{form};
    my $time = time();

    $self->create_temp_table();

    my $out .= CGI::start_form('post', CGI::self_url());
    $out .= CGI::h1("Choose the answers you want stats on...");
    $out .= CGI::table({ -align => 'center'}, $self->get_all_answers());

    $out .= CGI::submit(-name => 'action', 
                  -value => 'filteredstats',
                  -class => 'formsubmit');

    $out .= CGI::endform();
    return $out;
}

sub create_temp_table {
    my $self = shift;

    return if defined $self->{temp_table};

    my $form = $self->{form};
    my $db = $form->dbh_r();
    my $answerTable = $form->{dbAnswer};
    my $sql_structure = $self->get_sql_structure();
    my @tables = (keys %$sql_structure);
    my @columns = ();

    for my $table (keys %{$sql_structure}) {
	for my $col (@{$$sql_structure{$table}}) {
	    push @columns, $col;
	}
    }
    
    return 'No tables selected' unless defined $tables[0];

    my $select = join ", ", ($answerTable . '.id', @columns);
    my $from = join(", ", ($answerTable, @tables));

    my $where = $answerTable . ".id = " . $tables[0] . ".id";
    for (my $i = 0; $i < $#tables ; $i++) {
	$where .= " AND " . $tables[$i] . ".id = " . $tables[$i+1] . ".id";
    }

    if (CGI::param('show_id')) {
	my @t = map {"$answerTable.id = $_"} CGI::param('show_id');
	$where .= ' AND (' . join ( ' OR ', @t) . " ) \n";
    }

    print STDERR "FILTER SAYS SELECT IS  ", $select, "\n";
    print STDERR "FILTER SAYS FROM   IS  ", $from, "\n";
    print STDERR "FILTER SAYS WHERE  IS  ", $where, "\n";

    my $request = "CREATE TEMP TABLE $self->{name} AS select " . $select . "\n" .
	"from " . $from . "\n" .
	"where enquiry = \'" . $form->{enquiry} . "\' AND " . $where . ";\n";
    print STDERR 'REQUESTA  ', $request;

    my $sth = $db->prepare($request)
        || die "Cannot prepare create TEMP TABLE request: " . $db->errstr;
    $sth->execute()
        || die "Cannot execute create TEMP TABLE request: " . $db->errstr . "\n";

    $self->{sth} = $sth;
    $self->{temp_table} = 1;

    print STDERR 'SHOOOOOOOW IIIIIIIIIDDDDDDDDDD:', CGI::param('show_id'), "\n";
 
}
 
sub finish {
    my $self = shift;
    my $sth = $self->{sth};
    $sth->finish();
}


sub get_all_answers {
    my $self = shift;
    my $form = $self->{form};
    my $db = $form->dbh_r();
    my $sql_structure = $self->get_sql_structure();
    my @columns = ();

    for my $table (keys %{$sql_structure}) {
	for my $col (@{$$sql_structure{$table}}) {
	    push @columns, $col;
	}
    }

    my $request = "select * from $self->{name};";
    print STDERR $request, "\n";

    my $sth = $db->prepare($request);
    $sth->execute();
    #my $out = '';
    my $out = CGI::Tr ({ -class => 'statrow'}, CGI::th(['', 'id', @columns]));

    while (my $row = $sth->fetchrow_arrayref()) {
	print STDERR join " | ", @$row, "\n";
	$out .= CGI::Tr( { -class => 'statrow'},
			 CGI::td([CGI::checkbox( -name => 'show_id',
						 -checked => 1,
						 -value => $$row[0],
						 -label => ''),
				  @$row]));
    }
    $sth->finish();

    return $out;    
}


# A hash where the keys are the table names and the
# values are arrays of the columns in those tables
sub get_sql_structure {
    my $self = shift;
    my $form = $self->{form};
    my %tables = ();
    
    for my $section (@{$form->{sections}}) {
	my $table = $section->get_table_name();
	print STDERR "UNE TABLE:   $table .\n"; 
	#next unless defined CGI::param($table);

	for my $question (@{$section->{questions}}) {
	    print STDERR "UNE QUESTION:  ${$question->generate_field_names()}[0].\n"; 
	    #next unless defined CGI::param(${$question->generate_field_names()}[0]);
	    push @{$tables{$table}}, ${$question->generate_field_names()}[0];
	}
    }

    return \%tables;
}
1;

# end of FormStats.pm
